<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  
  <title>mysql的sql优化 | du小站</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="sql优化分析">
<meta property="og:type" content="article">
<meta property="og:title" content="mysql的sql优化">
<meta property="og:url" content="https://wenjies.github.io/2016/08/03/mysql%E7%9A%84sql%E4%BC%98%E5%8C%96/index.html">
<meta property="og:site_name" content="du小站">
<meta property="og:description" content="sql优化分析">
<meta property="og:locale">
<meta property="og:image" content="https://wenjies.github.io/images/mysql/y_h_1.jpg">
<meta property="og:image" content="https://wenjies.github.io/images/mysql/y_h_2.jpg">
<meta property="og:image" content="https://wenjies.github.io/images/mysql/y_h_3.jpg">
<meta property="og:image" content="https://wenjies.github.io/images/mysql/y_h_5.jpg">
<meta property="og:image" content="https://wenjies.github.io/images/mysql/y_h_6.jpg">
<meta property="og:image" content="https://wenjies.github.io/images/mysql/index_lose.png">
<meta property="og:image" content="https://wenjies.github.io/images/mysql/y_h_8.jpg">
<meta property="og:image" content="https://wenjies.github.io/images/mysql/y_h_4.jpg">
<meta property="og:image" content="https://wenjies.github.io/images/mysql/y_h_10.jpg">
<meta property="og:image" content="https://wenjies.github.io/images/mysql/sql_slow.jpg">
<meta property="og:image" content="https://wenjies.github.io/images/mysql/y_h_7.jpg">
<meta property="og:image" content="https://wenjies.github.io/images/mysql/y_h_9.jpg">
<meta property="og:image" content="https://wenjies.github.io/images/mysql/y_h_11.jpg">
<meta property="article:published_time" content="2016-08-03T01:00:00.000Z">
<meta property="article:modified_time" content="2024-12-11T02:40:03.976Z">
<meta property="article:author" content="杰">
<meta property="article:tag" content="mysql">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://wenjies.github.io/images/mysql/y_h_1.jpg">
  
    <link rel="alternate" href="/atom.xml" title="du小站" type="application/atom+xml">
  
  
    <link rel="shortcut icon" href="/favicon.png">
  
  
  
<link rel="stylesheet" href="/css/style.css">

  
    
<link rel="stylesheet" href="/fancybox/jquery.fancybox.min.css">

  
  
<meta name="generator" content="Hexo 7.3.0"></head>

<body>
  <div id="container">
    <div id="wrap">
      <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <div id="header-title" class="inner">
      <h1 id="logo-wrap">
        <a href="/" id="logo">du小站</a>
      </h1>
      
        <h2 id="subtitle-wrap">
          <a href="/" id="subtitle">我们笑着说再见，却深知再见遥遥无期。</a>
        </h2>
      
    </div>
    <div id="header-inner" class="inner">
      <nav id="main-nav">
        <a id="main-nav-toggle" class="nav-icon"><span class="fa fa-bars"></span></a>
        
          <a class="main-nav-link" href="/">Home</a>
        
          <a class="main-nav-link" href="/archives">Archives</a>
        
      </nav>
      <nav id="sub-nav">
        
        
          <a class="nav-icon" href="/atom.xml" title="RSS Feed"><span class="fa fa-rss"></span></a>
        
        <a class="nav-icon nav-search-btn" title="Suche"><span class="fa fa-search"></span></a>
      </nav>
      <div id="search-form-wrap">
        <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="Suche"><button type="submit" class="search-form-submit">&#xF002;</button><input type="hidden" name="sitesearch" value="https://wenjies.github.io"></form>
      </div>
    </div>
  </div>
</header>

      <div class="outer">
        <section id="main"><article id="post-mysql的sql优化" class="h-entry article article-type-post" itemprop="blogPost" itemscope itemtype="https://schema.org/BlogPosting">
  <div class="article-meta">
    <a href="/2016/08/03/mysql%E7%9A%84sql%E4%BC%98%E5%8C%96/" class="article-date">
  <time class="dt-published" datetime="2016-08-03T01:00:00.000Z" itemprop="datePublished">2016-08-03</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 class="p-name article-title" itemprop="headline name">
      mysql的sql优化
    </h1>
  

      </header>
    
    <div class="e-content article-entry" itemprop="articleBody">
      
        <h2 id="单值索引和复合索引的区别"><a href="#单值索引和复合索引的区别" class="headerlink" title="单值索引和复合索引的区别"></a>单值索引和复合索引的区别</h2><ol>
<li>首先来看没有索引的情况：可以看出 type 为 all 是全表扫描性能不高，优化就是建立索引。</li>
</ol>
<blockquote>
<p>EXPLAIN SELECT address,name FROM t_user u WHERE u.address&#x3D;’成都’ AND u.name&#x3D;’haha’</p>
</blockquote>
<p><img src="/../images/mysql/y_h_1.jpg"></p>
<hr>
<ol start="2">
<li>来看创建两个单值索引的情况：看到possible_keys为 idx_a,idx_n 但是key为idx_a说明只用了一个索引另一个索引没有使用（mysql会自动使用最优的索引）</li>
</ol>
<blockquote>
<p>CREATE INDEX idx_a ON t_user(address);<br>CREATE INDEX idx_n ON t_user(name);<br>EXPLAIN SELECT address,name FROM t_user u WHERE u.address&#x3D;’成都’ AND u.name&#x3D;’haha’</p>
</blockquote>
<p><img src="/../images/mysql/y_h_2.jpg"></p>
<hr>
<ol start="3">
<li>来看创建一个复合索引的情况：key为idx_a_n说明用了索引,ref为 const,const 说名这个复合索引对这两个字段都起作用。Extra中有Using index（覆盖索引）sql性能很好。</li>
</ol>
<blockquote>
<p>CREATE INDEX idx_a_n ON t_user(address,name);<br>EXPLAIN SELECT address,name FROM t_user u WHERE u.address&#x3D;’成都’ AND u.name&#x3D;’haha’</p>
</blockquote>
<p><img src="/../images/mysql/y_h_3.jpg"></p>
<hr>
<h2 id="join连接索引优化"><a href="#join连接索引优化" class="headerlink" title="join连接索引优化"></a>join连接索引优化</h2><ol>
<li>left join使用索引，索引应建立在右边。</li>
</ol>
<blockquote>
<p>分析“FROM t_user u LEFT JOIN t_user_role ur ON u.id&#x3D;ur.user_id LEFT JOIN t_role r ON ur.role_id &#x3D;r.id”索引该如何建立.<br>1、“t_user u LEFT JOIN t_user_role” 所以“u.id&#x3D;ur.user_id”中user_id应建索引<br>2、“LEFT JOIN t_role”所以“ur.role_id &#x3D;r.id”中id应建索引。因为id为主键所以本身就是主键索引。</p>
</blockquote>
<p><img src="/../images/mysql/y_h_5.jpg"></p>
<hr>
<ol start="2">
<li>RIGHT join使用索引，索引应建立在左边。</li>
</ol>
<blockquote>
<p>同理分析“FROM t_user u RIGHT JOIN t_user_role ur ON u.id&#x3D;ur.user_id RIGHT JOIN t_role r ON ur.role_id &#x3D;r.id”索引该如何建立.<br>1、“RIGHT JOIN t_user_role ur” 所以“ u.id&#x3D;ur.user_id”中id应建索引，因为id为主键所以本身就是主键索引。<br>2、“RIGHT JOIN t_role r”所以“ur.role_id &#x3D;r.id”中role_id应建索引。</p>
</blockquote>
<p><img src="/../images/mysql/y_h_6.jpg"></p>
<hr>
<h2 id="索引失效"><a href="#索引失效" class="headerlink" title="索引失效"></a>索引失效</h2><p><strong><code>索引失效的大致情况如下</code></strong><br><img src="/../images/mysql/index_lose.png"><br>1、如index(a,b,c)&#x3D;&gt; where b&#x3D;5 and c&#x3D;6 b,c不会使用索引.因为缺少a。<br>3、如index(a,b,c)&#x3D;&gt; where a&#x3D;1 and b&gt;5 and c&#x3D;6 那么a,b会用到索引，c不会使用索引. b是范围后面的都会中断。<br>7、可以使用覆盖索引的方式解决：like’%..%’索引失效。<br>9、可以使用 union 带替代 or</p>
<hr>
<h2 id="index列子分析"><a href="#index列子分析" class="headerlink" title="index列子分析"></a>index列子分析</h2><p>index(c1,c2,c3,c4) : order by</p>
<blockquote>
<p>where c1&gt;50 order by c2 出现:filesort。<br>where 1&#x3D;1 order by c1 asc,c2 desc 出现:filesort。<br>where 1&#x3D;1 order by c1 asc,c2 desc 出现:filesort。<br>where c1&#x3D;”c1” and c5&#x3D;’c5’ order by c3,c2 出现:filesort。<br>where c1&#x3D;”c1” and c5&#x3D;’c5’ order by c2,c3 不出现:filesort。<br>where c1&#x3D;”c1” and c5&#x3D;’c5’ and c2&#x3D;’c2’ order by c3,c2 不出现:filesort。</p>
</blockquote>
<p>index(c1,c2,c3,c4):group by</p>
<blockquote>
<p>where c1&#x3D;”c1” and c4&#x3D;’c4’ group by c2,c3 不出现:filesort、temp<br>where c1&#x3D;”c1” and c4&#x3D;’c4’ group by c3,c2 出现:filesort、temp。<br>where c1&#x3D;”c1” and c4&#x3D;’c4’ and c2&#x3D;’c2’ group by c2,c3 不出现:filesort、temp。</p>
</blockquote>
<p>group by:分组之前基本需要排序，可能会有临时表产生很耗性能。<br><img src="/../images/mysql/y_h_8.jpg"><br><img src="/../images/mysql/y_h_4.jpg"></p>
<hr>
<h2 id="小表驱动大表"><a href="#小表驱动大表" class="headerlink" title="小表驱动大表"></a>小表驱动大表</h2><p><img src="/../images/mysql/y_h_10.jpg"></p>
<pre><code>exists不一定性能比in高，exists是in的一个变种替代。
</code></pre>
<hr>
<h2 id="慢查询开启及分析"><a href="#慢查询开启及分析" class="headerlink" title="慢查询开启及分析"></a>慢查询开启及分析</h2><p><img src="/../images/mysql/sql_slow.jpg"></p>
<h2 id="profile"><a href="#profile" class="headerlink" title="profile"></a>profile</h2><blockquote>
<p>SHOW VARIABLES LIKE’profiling’;<br>SET profiling&#x3D;ON;<br>SHOW PROFILES;<br>SHOW PROFILE cpu,block io FOR QUERY Query_ID;或将 cpu,block io 改为 all</p>
</blockquote>
<p>若：Status中出现以下4个中一个必须优化。<br><img src="/../images/mysql/y_h_7.jpg"></p>
<hr>
<h2 id="全局查询日志"><a href="#全局查询日志" class="headerlink" title="全局查询日志"></a>全局查询日志</h2><blockquote>
<p>SET GLOBAL general_log&#x3D;1;#0为关闭 1为开启<br>SET GLOBAL log_output&#x3D;’Table’;</p>
</blockquote>
<p>执行：SELECT * FROM mysql.general_log; 将看到执行过的sql信息。<br><br>sql优化过程步骤：<br><img src="/../images/mysql/y_h_9.jpg"></p>
<h2 id="Mysql的锁"><a href="#Mysql的锁" class="headerlink" title="Mysql的锁"></a>Mysql的锁</h2><p><img src="/../images/mysql/y_h_11.jpg"></p>

      
    </div>
    <footer class="article-footer">
      <a data-url="https://wenjies.github.io/2016/08/03/mysql%E7%9A%84sql%E4%BC%98%E5%8C%96/" data-id="cm3zaz9c7000wtgu60poz6tww" data-title="mysql的sql优化" class="article-share-link"><span class="fa fa-share">Teilen</span></a>
      
      
      
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/mysql/" rel="tag">mysql</a></li></ul>

    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/2016/08/11/mysql%E8%87%AA%E5%AE%9A%E4%B9%89%E5%87%BD%E6%95%B0/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Neuer</strong>
      <div class="article-nav-title">
        
          mysql自定义函数
        
      </div>
    </a>
  
  
    <a href="/2016/08/02/join%E7%9A%847%E7%A7%8D%E6%96%B9%E5%BC%8F/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Älter</strong>
      <div class="article-nav-title">join的7种方式</div>
    </a>
  
</nav>

  
</article>


</section>
        
          <aside id="sidebar">
  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Kategorien</h3>
    <div class="widget">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/categories/AJAX/">AJAX</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/JAVA/">JAVA</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/MQ/">MQ</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Mybatis/">Mybatis</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Redis/">Redis</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Spring/">Spring</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/zookeeper/">zookeeper</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/%E5%88%86%E5%B8%83%E5%BC%8F%E4%BA%8B%E5%8A%A1/">分布式事务</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/%E5%B7%A5%E5%85%B7/">工具</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/%E7%BB%8F%E9%AA%8C/">经验</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tags</h3>
    <div class="widget">
      <ul class="tag-list" itemprop="keywords"><li class="tag-list-item"><a class="tag-list-link" href="/tags/aop/" rel="tag">aop</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/bean/" rel="tag">bean</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/hexo/" rel="tag">hexo</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/java/" rel="tag">java</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/js/" rel="tag">js</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/micrometer/" rel="tag">micrometer</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/mvc/" rel="tag">mvc</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/mybatis/" rel="tag">mybatis</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/mysql/" rel="tag">mysql</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/redis/" rel="tag">redis</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/rocketmq/" rel="tag">rocketmq</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/spring/" rel="tag">spring</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/zookeeper/" rel="tag">zookeeper</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E4%BA%8B%E5%8A%A1/" rel="tag">事务</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E5%88%86%E5%B8%83%E5%BC%8F%E4%BA%8B%E5%8A%A1/" rel="tag">分布式事务</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E5%8D%95%E5%85%83%E6%B5%8B%E8%AF%95/" rel="tag">单元测试</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E5%9F%BA%E7%A1%80%E7%9F%A5%E8%AF%86/" rel="tag">基础知识</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E5%AE%9A%E6%97%B6%E4%BB%BB%E5%8A%A1/" rel="tag">定时任务</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E6%89%A9%E5%B1%95/" rel="tag">扩展</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E6%95%B4%E5%90%88/" rel="tag">整合</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E9%9D%A2%E8%AF%95/" rel="tag">面试</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tag Cloud</h3>
    <div class="widget tagcloud">
      <a href="/tags/aop/" style="font-size: 10px;">aop</a> <a href="/tags/bean/" style="font-size: 10px;">bean</a> <a href="/tags/hexo/" style="font-size: 10px;">hexo</a> <a href="/tags/java/" style="font-size: 10px;">java</a> <a href="/tags/js/" style="font-size: 10px;">js</a> <a href="/tags/micrometer/" style="font-size: 10px;">micrometer</a> <a href="/tags/mvc/" style="font-size: 13.33px;">mvc</a> <a href="/tags/mybatis/" style="font-size: 10px;">mybatis</a> <a href="/tags/mysql/" style="font-size: 20px;">mysql</a> <a href="/tags/redis/" style="font-size: 10px;">redis</a> <a href="/tags/rocketmq/" style="font-size: 10px;">rocketmq</a> <a href="/tags/spring/" style="font-size: 10px;">spring</a> <a href="/tags/zookeeper/" style="font-size: 10px;">zookeeper</a> <a href="/tags/%E4%BA%8B%E5%8A%A1/" style="font-size: 10px;">事务</a> <a href="/tags/%E5%88%86%E5%B8%83%E5%BC%8F%E4%BA%8B%E5%8A%A1/" style="font-size: 10px;">分布式事务</a> <a href="/tags/%E5%8D%95%E5%85%83%E6%B5%8B%E8%AF%95/" style="font-size: 10px;">单元测试</a> <a href="/tags/%E5%9F%BA%E7%A1%80%E7%9F%A5%E8%AF%86/" style="font-size: 16.67px;">基础知识</a> <a href="/tags/%E5%AE%9A%E6%97%B6%E4%BB%BB%E5%8A%A1/" style="font-size: 10px;">定时任务</a> <a href="/tags/%E6%89%A9%E5%B1%95/" style="font-size: 10px;">扩展</a> <a href="/tags/%E6%95%B4%E5%90%88/" style="font-size: 10px;">整合</a> <a href="/tags/%E9%9D%A2%E8%AF%95/" style="font-size: 10px;">面试</a>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Archiv</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2025/01/">January 2025</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2024/11/">November 2024</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2017/10/">October 2017</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2017/09/">September 2017</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2017/07/">July 2017</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2017/05/">May 2017</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2016/12/">December 2016</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2016/11/">November 2016</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2016/10/">October 2016</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2016/08/">August 2016</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2016/05/">May 2016</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2016/03/">March 2016</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2016/02/">February 2016</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">letzter Beitrag</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/2025/01/10/spring%20boot3%E6%95%B4%E5%90%88ELK%E5%AE%9E%E7%8E%B0%E6%97%A5%E5%BF%97%E6%90%9C%E7%B4%A2/">spring boot3整合ELK实现日志搜索</a>
          </li>
        
          <li>
            <a href="/2025/01/10/spring%20boot3%E6%95%B4%E5%90%88%E9%93%BE%E8%B7%AF%E8%BF%BD%E8%B8%AA/">spring boot3整合链路追踪</a>
          </li>
        
          <li>
            <a href="/2024/11/27/hexo%E4%BD%BF%E7%94%A8/">hexo使用</a>
          </li>
        
          <li>
            <a href="/2017/10/17/spring%E5%8D%95%E5%85%83%E6%B5%8B%E8%AF%95/">spring单元测试</a>
          </li>
        
          <li>
            <a href="/2017/09/16/jsonp%E8%B7%A8%E5%9F%9F%E8%AF%B7%E6%B1%82/">jsonp跨域请求</a>
          </li>
        
      </ul>
    </div>
  </div>

  
</aside>
        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      
      &copy; 2025 杰<br>
      <a href="https://beian.miit.gov.cn/" target="_blank">蜀ICP备19041392号-1</a>
    </div>
  </div>
</footer>

    </div>
    <nav id="mobile-nav">
  
    <a href="/" class="mobile-nav-link">Home</a>
  
    <a href="/archives" class="mobile-nav-link">Archives</a>
  
</nav>
    


<script src="/js/jquery-3.6.4.min.js"></script>



  
<script src="/fancybox/jquery.fancybox.min.js"></script>




<script src="/js/script.js"></script>





  </div>
</body>
</html>